Release 10.1A: OpenEdge Development:
ProDataSets

Overview

If you run PickOrder.w and tab through all the filter fields without entering a value into one, you’ll see a small but noticeable delay before the Order browse displays the Orders. After all, Progress has to read nearly 4000 Order records from the database, plus their Customers and SalesReps, create a temp-table record for each of them, and buffer-copy the database records to the temp-table. Then it copies the ProDataSet definition and the entire contents of the ttOrder table to the window procedure. Considering the amount of work it’s doing, it’s pretty amazing that it doesn’t take a lot longer than it does. However, if the number of rows were even larger, or if you were running the support procedure across an AppServer connection on a different machine, the delay would be much greater.

Generally, you should try to avoid giving your users the opportunity to browse through very large numbers of rows on the client, instead prompting them to filter the data in advance as the example window does. However, in some cases you need to move a potentially large number of rows from server to client, and it is often better to do it in batches so the user can see some of the rows before every database record has been read and copied into the temp-table and across to the client. This section extends the example from Chapter 7, " Advanced Events and Attributes."to show you a way of doing this. At the same time we’ll show you how to limit the number of fields copied into the temp-table. After all, the window is only showing four fields from the ttOrder table, so there is really no point in copying every field into the temp-table, and more significantly, passing all those field values across to the client where they will never be seen or used.

To update the code:

To get started, copy the PickOrder.w procedure to PickOrderBatch.w, and the OrderSupport.p to OrderSupportBatch.p.
Change the RUN statement in the Main Block of PickOrderBatch.w to start OrderSupportBatch.p.
In the LEAVE trigger for cSalesRep in PickOrderBatch.w, change the RUN statement so that you run a different support procedure if the user doesn’t enter a value into any of the filter fields, as shown:

IF cSelection NE "" THEN /* There were selection criteria */ RUN fetchOrders IN hOrderProc (INPUT cSelection,                                      OUTPUT DATASET dsOrder). ELSE /* No selection so retrieve (the first) batch of rows. */ RUN fetchOrderBatch IN hOrderProc (INPUT 0, /* Start at the first Order */ INPUT "OrderNum,CustNum,SalesRep,OrderDate", OUTPUT DATASET dsOrder).

The new procedure fetchOrderBatch takes an Order Number to start with, and a list of fields to populate the temp-table with. Since you want to start fresh when the user tabs out of the SalesRep, you just pass 0 as the starting point in the Order table. The four fields in the second parameter are the fields the browse uses; those are the only ones you need values for on the client.

The OUTPUT parameter is the same ProDataSet as before.

Switch over to OrderSupportBatch.p. First you need a new definition at the top, as shown:

DEFINE VARIABLE cFieldList AS CHARACTER NO-UNDO.

The cFieldList is the list of fields to include in the ttOrder table, passed over to fetchOrderBatch.

Write the fetchOrderBatch procedure. It needs the three parameters you saw in the SalesRep trigger. For example:

PROCEDURE fetchOrderBatch: DEFINE INPUT PARAMETER piLastOrder AS INTEGER NO-UNDO. DEFINE INPUT PARAMETER pcFieldList AS CHARACTER NO-UNDO. DEFINE OUTPUT PARAMETER DATASET FOR dsOrder BY-VALUE.

The selection that becomes the where-clause for the Order query needs to start with the first Order Number greater than the one passed in. For the call in the SalesRep trigger, this is the first Order in the database. In later calls, the INPUT parameter will be the highest Order Number retrieved so far.

The pcFieldList parameter passed in is saved in the variable cFieldList, which can be seen throughout the procedure. For example:

ASSIGN cSelection = "OrderNum > " + STRING(piLastOrder) cFieldList = pcFieldList.

You use the BATCH-SIZE attribute on the ttOrder buffer to tell Progress to fill only a maximum of 20 rows into the ttOrder temp-table at a time, as shown:

hDataSet:GET-BUFFER-HANDLE(1):BATCH-SIZE = 20.

The next four lines are the same as in fetchOrder, and you can copy them from there:

hDataSet:EMPTY-DATASET. hDataSet:GET-BUFFER-HANDLE(2):FILL-MODE = "NO-FILL". /* ttOline */ hDataSet:GET-BUFFER-HANDLE(3):FILL-MODE = "NO-FILL". /* ttItem */ hDataSet:FILL().